import os
import requests
import json
import pandas as pd
from lxml import etree
from tools.database import SQLite
from datetime import datetime,timedelta
def get_database_path():
    base_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..'))
    db_path = os.path.join(base_dir, 'instance', 'admin_system.db')
    return db_path

class jl:
    def get_shiduan_sql():
        """获取当前的时间范围"""
        now = datetime.now()
        hour = now.hour + now.minute / 60
        
        if hour >= 11 and hour < 12:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=8.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<11.5"
            s="H1"
        elif hour >= 14 and hour < 15:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=11.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<15.5"
            s="H2"
        elif hour >= 17 and hour < 18:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=15.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<17.5"
            s="H3"
        elif hour >= 20 and hour < 21:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=17.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<20.5"
            s="H4"
        elif hour >=23 and hour < 24:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=20.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<23.5"
            s="H5"
        elif hour >= 2 and hour < 3:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=23.5 or (HOUR(TestTime)+MINUTE(TestTime)/60)<3.5"
            s="H6"
        elif hour >= 5 and hour < 6:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=3.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<5.5"
            s="H7"
        elif hour >= 8 and hour < 9:
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=5.5 and (HOUR(TestTime)+MINUTE(TestTime)/60)<8.5"
            s="H8"
        else:
            s="H9"
            sql = "(HOUR(TestTime)+MINUTE(TestTime)/60)>=23.5 or (HOUR(TestTime)+MINUTE(TestTime)/60)<3.5"
        sss=f"""
                SELECT
                    BinFileName,
                    Class,
                    AVG(Eta) AS AvgEta,
                    COUNT(*) AS RecordCount
                FROM
                    Results
                WHERE
                    {sql}
                GROUP BY
                    BinFileName,
                    Class
                ORDER BY
                    BinFileName,
                    Class
                """
        return sss,s

    def get_value():
        variable = {}
        db_manager = SQLite(get_database_path())
        if db_manager.connect():
            try:
                df = pd.read_sql_query('SELECT * FROM variable', db_manager.conn)
                for _, row in df.iterrows():
                    variable[row['key']] = row['value']
            except Exception as e:
                print(f"数据获取失败：{e}")
            finally:
                db_manager.close()
        return variable

    def get_ip():
        db_manager = SQLite(get_database_path())
        if db_manager.connect():
            try:
                df = pd.read_sql_query('SELECT * FROM ip_config', db_manager.conn)
            except Exception as e:
                print(f"数据获取失败：{e}")
            finally:
                db_manager.close()
        return df

    def get_product():
        product = {}
        db_manager = SQLite(get_database_path())
        if db_manager.connect():
            try:
                df = pd.read_sql_query('SELECT * FROM product', db_manager.conn)
                for _, row in df.iterrows():
                    product[row['product_name']] = {
                        '档位': row['nominal_grade'],
                        '合并档位': row['merged_grade'],
                        '合并档': row['merge_file'],
                    }
            except Exception as e:
                print(f"数据获取失败：{e}")
            finally:
                db_manager.close()
        return product

    def get_current_shift_info():
        """获取当前的班次信息（日期、班次）"""
        now = datetime.now()
        hour = now.hour + now.minute / 60
        today = datetime.today()
        if hour < 8.5:
            # 早班之前，属于前一天的夜班
            today = today - timedelta(days=1)
            dateiv = today.strftime("%Y-%#m-%#d")
            datetpe = today.strftime("%Y-%m-%d")
            datef=today.strftime("%Y%m%d")
            shift = "N"
        elif hour > 20.5:
            # 晚班时间
            dateiv = today.strftime("%Y-%#m-%#d")
            datetpe = today.strftime("%Y-%m-%d")
            datef=today.strftime("%Y%m%d")
            shift = "N"
        else:
            # 白班时间
            dateiv = today.strftime("%Y-%#m-%#d")
            datetpe = today.strftime("%Y-%m-%d")
            datef=today.strftime("%Y%m%d")
            shift = "D"

        return dateiv,datetpe, shift,datef

class itw:
    def send_message(url,txt):
        # 发送消息到i通威2
        header = {
        "Content-Type": "application/json",
        }
        data={
            "msgtype":"text",
            "text":{
                "content": txt
            }
        }
        message_json = json.dumps(data)
        info = requests.post(url=url,data=message_json,headers=header)#i通威发消息
class ini:
    # 读取 ini 配置文件键的函数
    def read_ini_key(ini_path, section='Classification', key='CurrentClassification'):
        try:
            if not os.path.exists(ini_path):
                print(f"文件不存在: {ini_path}")
                return None
            target_section = f'[{section}]'
            with open(ini_path, 'r', encoding='utf-8') as file:
                in_target_section = False
                for line in file:
                    line = line.strip()
                    if line == target_section:
                        in_target_section = True
                    elif line.startswith('[') and line.endswith(']'):
                        if in_target_section:
                            break
                        continue
                    elif in_target_section:
                        equal_index = line.find('=')
                        if equal_index != -1:
                            current_key = line[:equal_index].strip()
                            if current_key == key:
                                return line[equal_index + 1:].strip()
            print(f"未找到指定的 section: {section} 或 key: {key}")
            return None
        except Exception as e:
            print(f"读取文件时出错: {e}")
            return None

    def get_xml_data(xml_path, xpathvalue):
        try:
            # 检查文件是否存在
            if not os.path.exists(xml_path):
                print(f"文件不存在: {xml_path}")
                return []
            tree = etree.parse(xml_path)
            return tree.xpath(xpathvalue)
        except Exception as e:
            print(f"数据获取失败：{e}")
            return []